package com.jqb.jspcart.service.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;

import com.jqb.jspcart.bean.Hardware;
import com.jqb.jspcart.util.SQLHandler;

public class HardwareServiceImpl {
	
	/**
	 * 根据分类查询返回集合(Oralce)
	 * select 字段1,字段2 from (select 字段1,字段2,rownum rid from 表名 where rownum
	 * <=pageSize*pageNum) t where t.rid > pageSize*(pageNum-1)
	 */
	public ArrayList<Hardware> getHardwareList(int pageSize,int pageNum,String sort) throws SQLException {
		ArrayList<Hardware> hardwareList = new ArrayList<Hardware>();
		ArrayList<Object[]> obj = new ArrayList<Object[]>();
		String sql = "select id,name,price,inventory,sort,brand from (select id,name," +
				"price,inventory,sort,brand,rownum rid from hardware " +
				"where rownum<=? and sort=?) where rid>? and sort=?";
		String[] params = {pageSize*pageNum+"",sort,pageSize*(pageNum-1)+"",sort};
		obj = SQLHandler.newInstance().executeQuery(sql, params);
		Iterator<Object[]> it = obj.iterator();
		while(it.hasNext()){
			Hardware hardware = new Hardware();
			Object[] row = it.next();
			hardware.setId(Integer.parseInt(row[0]+""));
			hardware.setName((String)row[1]);
			hardware.setPrice(Float.parseFloat(row[2]+""));
			hardware.setInventory(Integer.parseInt(row[3]+""));
			hardware.setSort(sort);
			hardware.setBrand((String)row[5]);
			hardwareList.add(hardware);
		}
		return hardwareList;
	}
	
	/**
	 * 查询所有分类
	 */
	public ArrayList<String> getHardwareSort() throws Exception{
		ArrayList<String> hardwareSort = new ArrayList<String>();
		ArrayList<Object[]> obj = new ArrayList<Object[]>();
		String sql = "select DISTINCT sort from hardware where 1=?";
		String[] params = {1+""};
		obj = SQLHandler.newInstance().executeQuery(sql, params);
		Iterator<Object[]> it = obj.iterator();
		while(it.hasNext()){
			Object[] o = it.next();
			hardwareSort.add(o[0]+"");
		}
		return hardwareSort;
	}
	
	/**
	 * 返回满足条件的记录数
	 */
	public int getHardwareCount(String sort) throws SQLException{
		String sql = "select count(id) from hardware where sort='"+sort+"'";
		int count = SQLHandler.newInstance().executeQuery(sql);
		return count;
	}
	
	/**
	 * 通过Id获得记录 
	 */
	public Hardware getHardware(int id) throws SQLException{
		String sql = "select id,name,price,inventory,sort,brand from hardware where id=?";
		ArrayList<Object[]> obj = new ArrayList<Object[]>();
		String[] params={id+""};
		obj = SQLHandler.newInstance().executeQuery(sql, params);
		Hardware hardware = new Hardware();
		Iterator<Object[]> it = obj.iterator();
		while(it.hasNext()){
			Object[] row = it.next();
			hardware.setId(Integer.parseInt(row[0]+""));
			hardware.setName((String)row[1]);
			hardware.setPrice(Float.parseFloat(row[2]+""));
			hardware.setInventory(Integer.parseInt(row[3]+""));
			hardware.setSort((String)row[4]);
			hardware.setBrand((String)row[5]);
		}
		return hardware;
	}
	
	public static HardwareServiceImpl newInstance(){
		return new HardwareServiceImpl();
	}
}
